﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmProspectsCasesNew

    Dim FolderPath As String = FTPAddress + "Prospects Cases"

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If txtProspName.Text.Length = 0 Then
            Me.ErrProv.SetError(txtProspName, "Please fill in all required fields")
            Me.txtProspName.Focus()

        ElseIf txtBusinessSize.Text.Length = 0 Then
            Me.ErrProv.SetError(txtBusinessSize, "Please fill in all required fields")
            Me.txtBusinessSize.Focus()

        ElseIf txtInsurBroker.Text.Length = 0 Then
            Me.ErrProv.SetError(txtInsurBroker, "Please fill in all required fields")
            Me.txtInsurBroker.Focus()

        ElseIf txtInsurComp.Text.Length = 0 Then
            Me.ErrProv.SetError(txtInsurComp, "Please fill in all required fields")
            Me.txtInsurComp.Focus()

        ElseIf txtTargetRate.Text.Length = 0 Then
            Me.ErrProv.SetError(txtTargetRate, "Please fill in all required fields")
            Me.txtTargetRate.Focus()

        ElseIf CombLineofBusiness.SelectedIndex = -1 Then
            Me.ErrProv.SetError(CombLineofBusiness, "Please fill in all required fields")
            Me.CombLineofBusiness.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand()
                Dim Trans As SqlTransaction
                Dim ProsNo, Year, SNo As Integer
                Dim ProsType, ClassOfBusiness As String

                'Check Prospect type
                If Me.RGeneral.Checked = True Then
                    ProsType = "GEN"
                    ClassOfBusiness = "General"

                ElseIf Me.RMedical.Checked = True Then
                    ProsType = "MED"
                    ClassOfBusiness = "Medical"

                ElseIf Me.RLife.Checked = True Then
                    ProsType = "LIF"
                    ClassOfBusiness = "Life"

                ElseIf RMotor.Checked = True Then
                    ProsType = "MOT"
                    ClassOfBusiness = "Motor"
                End If

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Get prosp. case no. based on (Class, Year)
                cmd.CommandText = "Select IsNull(Max(PNo),0) From ProspectCases Where Year(TransDate)=Year(GetDate()) and ProsType=N'" & ProsType & "'"
                ProsNo = CInt(cmd.ExecuteScalar) + 1

                cmd.CommandText = "Select Year(GetDate())"
                Year = CInt(cmd.ExecuteScalar)

                cmd.CommandText = "Insert Into ProspectCases (Branch,PNo,ProsNo,ProsType,ProspectID,ProspectName,Status,TargetRate,Notes,InsuranceCompany," & _
                                  "InsuranceBroker,ClassofBusiness,LineofBusiness,BusinessSize,ExpiryDate,AssignedTo,AssignedBy,AssignedDate,TransDate,SavedUser) Values " & _
                                  "(@Branch,@PNo,@ProsNo,@ProsType,@ProspectID,@ProspectName,@Status,@TargetRate,@Notes," & _
                                  "@InsuranceCompany,@InsuranceBroker,@ClassofBusiness,@LineofBusiness,@BusinessSize,@ExpiryDate,@AssignedTo,@AssignedBy,GetDate(),GetDate(),@SavedUser) " & _
                                  "Select SCOPE_IDENTITY()"

                'Add values
                cmd.Parameters.AddWithValue("@Branch", Branch)
                cmd.Parameters.AddWithValue("@PNo", ProsNo)
                cmd.Parameters.AddWithValue("@ProsNo", "PC-" & BranchCode & "-" & Year & "-" & ProsType & "-" & ProsNo)
                cmd.Parameters.AddWithValue("@ProsType", ProsType)
                cmd.Parameters.AddWithValue("@ProspectID", Me.txtProspID.Text.Trim)
                cmd.Parameters.AddWithValue("@ProspectName", Me.txtProspName.Text.Trim)
                cmd.Parameters.AddWithValue("@Status", "Registered")
                cmd.Parameters.AddWithValue("@TargetRate", Me.txtTargetRate.Text)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text)
                cmd.Parameters.AddWithValue("@InsuranceCompany", Me.txtInsurComp.Text.Trim)
                cmd.Parameters.AddWithValue("@InsuranceBroker", Me.txtInsurBroker.Text.Trim)
                cmd.Parameters.AddWithValue("@ClassofBusiness", ClassOfBusiness)
                cmd.Parameters.AddWithValue("@LineofBusiness", Me.CombLineofBusiness.Text)
                cmd.Parameters.AddWithValue("@BusinessSize", Me.txtBusinessSize.Text.Trim)

                'Check if there is an existing policy that will expire
                If Me.ChExpiryDate.Checked = True Then
                    cmd.Parameters.AddWithValue("@ExpiryDate", Me.DTPExpiryDate.Value.ToShortDateString & " 10:10:10 AM")
                Else
                    cmd.Parameters.AddWithValue("@ExpiryDate", DBNull.Value)
                End If
                cmd.Parameters.AddWithValue("@AssignedTo", CurrentUser)
                cmd.Parameters.AddWithValue("@AssignedBy", CurrentUser)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                SNo = CInt(cmd.ExecuteScalar())

                'Add Note to Know Start Quotation Date
                cmd.CommandText = "Insert Into ProspectsNotes (ProsNo,Notes,SavedUser) Values " & _
                                  "(@ProsNo,@Notes,@SavedUser)"

                'Add Values
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@ProsNo", SNo)
                cmd.Parameters.AddWithValue("@Notes", "Prospect case registered")
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                'Create directory to store files
                Dim Dir As New DirectoryInfo(FolderPath)
                Dir.CreateSubdirectory("PC-" & BranchCode & "-" & Year & "-" & ProsType & "-" & ProsNo)

                MsgBox("Saved Successfully", MsgBoxStyle.Information, Me.Text)

                'Clear
                Me.txtInsurComp.Clear()
                Me.txtInsurBroker.Clear()
                Me.ChExpiryDate.Checked = False
                Me.txtBusinessSize.Clear()
                Me.txtInsurComp.Focus()
                Me.txtNotes.Clear()
                Me.txtTargetRate.Clear()
                Me.CombLineofBusiness.SelectedIndex = -1

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Sub FillLines()
        Try
            Dim cmd As New SqlCommand("Select Distinct LineofBusiness From InsurLinesofBusiness", cnn4)
            Dim Reader As SqlDataReader

            cnn4.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                CombLineofBusiness.Items.Add(Reader(0))
            End While
            cnn4.Close()
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub


    Sub Clear()
        Me.txtProspID.Clear()
        Me.txtProspName.Clear()
        Me.txtNotes.Clear()
        Me.txtTargetRate.Clear()
        Me.txtInsurComp.Text = ""
        Me.txtInsurBroker.Text = ""
        Me.ChExpiryDate.Checked = False
        Me.txtBusinessSize.Clear()
        Me.txtNotes.Clear()
        Me.txtTargetRate.Clear()
        Me.txtProspID.Focus()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchID.Click
        Dim a As New frmSearchProspect
        a.ShowDialog()

        If SelProspectID = 0 Then
            Exit Sub
        End If

        Me.txtProspID.Text = SelProspectID
        Me.txtProspName.Text = SelProspectName
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub txtProspectID_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtProspID.KeyUp
        If e.KeyCode = Keys.Enter Then
            If Me.txtProspID.Text.Trim.Length > 0 Then
                Me.txtProspName.Text = GetProspectName(CInt(Me.txtProspID.Text.Trim))
            End If
        End If
    End Sub

    Private Sub txtProspID_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtProspID.TextChanged
        Me.txtProspName.Clear()
    End Sub

    Private Sub ChExpiryDate_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChExpiryDate.CheckedChanged
        If Me.ChExpiryDate.Checked = True Then
            Me.DTPExpiryDate.Enabled = True
            Me.DTPExpiryDate.Value = Today.Date

        Else
            Me.DTPExpiryDate.Enabled = False
            Me.DTPExpiryDate.Value = Today.Date
        End If
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Clear()
    End Sub

    Private Sub frmProspectsCasesNew_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillLines()
    End Sub
End Class